package com.hy.dao.impl;

import com.hy.Vo.ExamineFlowDetailPurchaseApplyVo;
import com.hy.bean.ExamineFlowDetail;
import com.hy.dao.ExamineFlowDetailDao;
import com.hy.utils.DBManager;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

public class ExamineFlowDetailDaoimpl implements ExamineFlowDetailDao {
    private final QueryRunner queryRunner = new QueryRunner();

    @Override
    public ExamineFlowDetailPurchaseApplyVo queryById(Integer id) {
        Connection connection = null;
        String sql = "select efd.id id,efd.title title,efd.purchase_apply_id purchaseApplyId,efd.apply_type applyType,efd.examine_link examineLink,efd.start_time startTime, efd.examine_id examineId,c2.username username,examine_opinion examineOpinion,efd.examine_time examineTime from examine_flow_detail efd join purchase_apply pa on efd.purchase_apply_id = pa.id left join consumer c2 on efd.examine_id = c2.id where efd.id = ?";
        try {
            connection = DBManager.getConnection();
            return queryRunner.query(connection,sql,new BeanHandler<>(ExamineFlowDetailPurchaseApplyVo.class),id);
        }catch (Exception e){
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public List<ExamineFlowDetailPurchaseApplyVo> listAll(Integer status){
        Connection connection = null;
        String sql = "select efd.id id,efd.title title,efd.purchase_apply_id purchaseApplyId,efd.apply_type applyType,efd.examine_link examineLink,efd.start_time startTime, efd.examine_id examineId,c2.username username,examine_opinion examineOpinion,efd.examine_time examineTime from examine_flow_detail efd join purchase_apply pa on efd.purchase_apply_id = pa.id left join consumer c2 on efd.examine_id = c2.id where pa.status = ?";
        try {
            connection = DBManager.getConnection();
            return queryRunner.query(connection,sql,new BeanListHandler<>(ExamineFlowDetailPurchaseApplyVo.class),status);
        }catch (Exception e){
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public List<Map<String, Object>> listDeptIdOrMoney(ExamineFlowDetail examineFlowDetail) {
        Connection conn = null;
        String sql = "select pa.dept_id,p.money from examine_flow_detail e\n" +
                "inner join purchase_apply_detail p \n" +
                "on e.purchase_apply_id = p.purchase_apply_id\n" +
                "inner join purchase_apply pa\n" +
                "on pa.id = p.purchase_apply_id\n" +
                "inner join deptfunds d\n" +
                "on d.dept_id = pa.dept_id\n" +
                "where e.id = ? and pa.status = '0' and p.money <= d.balance";
        try {
            conn = DBManager.getConnection();
            return queryRunner.query(conn,sql,new MapListHandler(),examineFlowDetail.getId());
        }catch (Exception e){
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public long count(ExamineFlowDetail examineFlowDetail) {
        return 0;
    }

    @Override
    public int insert(ExamineFlowDetail examineFlowDetail) {
        Connection connection = null;
        String sql = "INSERT INTO examine_flow_detail values(seq_examine_flow_detail.nextval,?,?,?,?,to_date(?,'yyyy-mm-dd'),null,null,null)";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            connection = DBManager.getConnection();
            return queryRunner.update(connection,sql,examineFlowDetail.getTitle(),examineFlowDetail.getPurchaseApplyId(),examineFlowDetail.getApplyType(),examineFlowDetail.getExamineLink(),sdf.format(examineFlowDetail.getStartTime()));
        }catch (Exception e){
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    @Override
    public int insertBatch(List<ExamineFlowDetail> entities) {
        return 0;
    }

    @Override
    public int insertOrUpdateBatch(List<ExamineFlowDetail> entities) {
        return 0;
    }

    @Override
    public int update(Connection connection,ExamineFlowDetail examineFlowDetail) throws SQLException {
        String sql = "update examine_flow_detail set title = ?,purchase_apply_id = ?,apply_type = ?,examine_link = ?,start_time = to_date(?,'yyyy-mm-dd'),examine_id = ?,examine_opinion= ?,examine_time = to_date(?,'yyyy-mm-dd') where id = ?";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            return queryRunner.update(connection,sql,examineFlowDetail.getTitle(),examineFlowDetail.getPurchaseApplyId(),examineFlowDetail.getApplyType(),examineFlowDetail.getExamineLink(),sdf.format(examineFlowDetail.getStartTime()),examineFlowDetail.getExamineId(),examineFlowDetail.getExamineOpinion(),sdf.format(examineFlowDetail.getExamineTime()),examineFlowDetail.getId());
    }

    @Override
    public int deleteById() {
        return 0;
    }
}
